<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Im_cases_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    public function get_users(){
        return $this->db->query('SELECT * FROM app_users')->result();
    }

    public function get_currval(){
      return $this->db->query('SELECT get_nextval("CASE_NUM") as val')->result();
    }

    public function search_num($id){
      return $this->db->query('SELECT * FROM ar_account WHERE acct_num = "'.$id.'" ')->result();
    }

    public function get_inv_access($id){
        $sql = "SELECT * FROM inv_officer_access WHERE user_id = ".$id." ";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_reqNum($val){
        $this->db->query("call get_request_num(?,@val)", $val);
        $que = $this->db->query("SELECT @val as val");
        return $que->result();
    }

    public function get_inv_users($id,$role){
      $and;
      if($role == 'REQ'){ $and = " AND io.role_code = 'REQ_OFFCR' ";}
      if($role == 'APR'){ $and = " AND io.role_code = 'APRV_OFFCR' ";}
      if($role == 'RLS'){ $and = " AND io.role_code = 'RLS_OFFCR' ";}
      $sql    = "SELECT io.* , au.user_name , au.last_name , au.first_name FROM `inv_officers` io
                    INNER JOIN app_users au on au.user_id = io.user_id
                WHERE au.company_id = ".$id." ".$and." AND au.enable_flag = 'Y' ";
      $query  = $this->db->query($sql);
      return $query->result();
    }

    public function search($data){
        $arr = array();
        if(!empty($data['case_no'])){
          array_push($arr," case_no LIKE '". $data['case_no'] ."' ");
        }
        if(!empty($data['case_date'])){
          array_push($arr," case_date LIKE '". $data['case_date'] ."' ");
        }
        if(!empty($data['case_type'])){
          array_push($arr," case_type LIKE '". $data['case_type'] ."' ");
        }
        if(!empty($data['caller_acct_id'])){
          array_push($arr," caller_acct_id LIKE '". $data['caller_acct_id'] ."' ");
        }
        if(!empty($data['category'])){
          array_push($arr," category LIKE '". $data['category'] ."' ");
        }
        if(!empty($data['sub_category'])){
          array_push($arr," sub_category LIKE '". $data['sub_category'] ."' ");
        }
        if(!empty($data['department'])){
          array_push($arr," department LIKE '". $data['department'] ."' ");
        }
        if(!empty($data['activity_status'])){
          array_push($arr," activity_status LIKE '". $data['activity_status'] ."' ");
        }
        if(!empty($data['case_status'])){
          array_push($arr," case_status LIKE '". $data['case_status'] ."' ");
        }
        if(!empty($data['my_cases'])){
          array_push($arr," assigned_to = '". $data['my_cases'] ."' ");
        }
        $sql = "WHERE ".implode(' AND ',$arr);
        $q = $this->db->query('SELECT * FROM im_cases_v '.$sql);
        return $q->result();
    }

    public function insert_case($data){
        $this->db->query("call im_cases_ins(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,@id,@err,@msg)", $data);
        $que = $this->db->query("SELECT @id as id , @err as error , @msg as msg");
        echo json_encode($que->result());
    }

    public function update_case($data){
        $this->db->query("call im_cases_upd(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,@err,@msg)", $data);
        $que = $this->db->query("SELECT  @err as error , @msg as msg");
        echo json_encode($que->result());
    }

    public function insert_history($data){
        $this->db->query("call im_case_history_ins(?,?,?,?,?,?,?,?,?,@err,@msg)", $data);
        $que = $this->db->query("SELECT @err as err , @msg as msg");
        echo json_encode($que->result());
    }

    public function get_im_case_by_id($id){
        return $this->db->query('SELECT * FROM im_cases_v WHERE case_id = "'.$id.'" ')->result();
    }

    public function get_history_by_case_id($id){
        return $this->db->query('SELECT * FROM im_cases_history_v WHERE case_id = "'.$id.'" ORDER BY action_date DESC')->result();
    }

    public function get_related_doc_type_values($type){
      $q = '';
      switch ($type) {
        case 'INVOICE':
          $q = $this->db->query('SELECT invoice_id as id, invoice_num as num FROM ubs_invoices')->result();
          break;
        case 'WORK_ORDER':
          $q = $this->db->query('SELECT maintain_id as id, maintain_id as num FROM ubs_work_order')->result();
          break;
        default:
          # code...
          break;
      }
      return $q;
    }

    public function get_categories(){
        return $this->db->query('SELECT * FROM im_cases_category_v')->result();
    }

    public function get_sub_category_by_id($id){
        return $this->db->query('SELECT * FROM im_cases_sub_category_v WHERE sub_category_id = "'.$id.'"')->result();
    }

    public function get_sub_category_by_category_id($id){
        return $this->db->query('SELECT * FROM im_cases_sub_category_v WHERE category_id = "'.$id.'"')->result();
    }

    public function get_category_by_id($id){
        return $this->db->query('SELECT * FROM im_cases_category_v WHERE category_id = "'.$id.'"')->result();
    }

    public function getWorkOrderCaseId($id){
      return $this->db->query('SELECT * FROM ubs_work_order WHERE case_id = "'.$id.'"')->result();
    }

    public function getInvoiceCaseId($id){
      return $this->db->query('SELECT * FROM ubs_invoices WHERE case_id = "'.$id.'"')->result();
    }

    public function get_user_by_department($id){
      return $this->db->query('SELECT * FROM app_users WHERE department = "'.$id.'"')->result();
    }
    
    public function get_role_by_user_id(){
        return $this->db->query('SELECT * FROM app_users_v WHERE user_id = "'.$this->session->userdata('user_id').'"')->result();
    }

}